from time import sleep
from itemadapter import ItemAdapter
from .items import *
from .mysql.mysql_utils.mysql_conf import MySQLS
from .mysql.mysql_utils.mysql_conn import MysqlPooledDB


class companyPipeline:
	sql = ''
	fields = ''
	value = ''

	def open_spider(self, spider):
		self.data_list = []
		self.count = 0
		self.conn, self.cursor = MysqlPooledDB(MySQLS['me']).connect()
		spider.conn, spider.cursor = MysqlPooledDB(MySQLS['me']).connect()

	def count_num(self):
		if self.count % 100 == 0:
			print(f'已经爬取{self.count}条数据')

	def sql_com(self, base, gmt_create=None):
		if not gmt_create:
			sql = '''
                insert IGNORE into {}(%s) values(%s)
            '''.format(base)
		else:
			sql = '''
                insert IGNORE into {}(%s, gmt_create) values(%s, now())
            '''.format(base)
		return sql

	def process_item(self, item, spider):
		if isinstance(item, DataList):
			sql = self.sql_com('data_list', True)
			self.comm(item, sql, False)

		elif isinstance(item, DataInfo):
			sql = self.sql_com('data_info')
			self.comm(item, sql, False)
		return item

	def comm(self, item, sql, flag=True):
		# 默认多条插入
		if flag:
			self.flag_true(item, sql)
		else:
			self.flag_false(item, sql)

	def close_spider(self, spider):
		if self.data_list:
			try:
				print(f'data_list剩余数据{len(self.data_list)}, 准备存入')
				self.cursor.executemany(self.sql, self.data_list)
				self.conn.commit()
				self.data_list.clear()
			except Exception as e:
				print(e)
				print(self.sql, self.data_list)

		# 关闭mysql连接池
		self.cursor.close()
		self.conn.close()
		spider.cursor.close()
		spider.conn.close()

	# 多条插入
	def flag_true(self, item, sql):
		item_ = dict(item)
		fields = ','.join(item_)
		value = ','.join(["%s" for key in item_])
		if not self.sql:
			self.sql = sql % (fields, value)
		self.data_list.append(tuple(item_.values()))
		try:
			# 当数据data_list 为1000的整数 就往数据库批量插入
			if len(self.data_list) == 1000:
				self.cursor.executemany(sql % (fields, value), self.data_list)
				self.conn.commit()
				self.data_list.clear()
				self.count += 1000
				self.count_num()
		except Exception as e:
			print(e)
			self.data_list.clear()
			self.conn.rollback()

	# 单条插入
	def flag_false(self, item, sql):
		item = dict(item)
		fields = ','.join(item)
		value = ','.join(["%%(%s)s" % key for key in item])
		try:
			self.count_num()
			self.cursor.execute(sql % (fields, value), item)
			self.conn.commit()
			self.count += 1
		except Exception as e:
			print(e)
			print(sql % (fields, value) % item)
			self.conn.rollback()


